package de.openlaboratory.elib.test.database;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.junit.AfterClass;
import org.junit.BeforeClass;
import org.junit.Test;
import de.openlaboratory.elib.database.hibernate.HibernateUtil;
import de.openlaboratory.elib.test.model.Department;
import de.openlaboratory.elib.test.model.Employee;

public class HibernateConnectivity
{
	@BeforeClass
	public static void setUpBeforeClass() throws Exception
	{
		HibernateUtil.getSessionFactory();
	}

	@AfterClass
	public static void tearDownAfterClass() throws Exception
	{
		HibernateUtil.getSessionFactory().close();
	}

	@Test
	public void testPooledConnection()
	{
		SessionFactory sf = HibernateUtil.getSessionFactory();
		Session session = sf.openSession();
		session.beginTransaction();

		Department department = new Department();
		department.setDepartmentName("Sales");
		session.save(department);

		Employee emp1 = new Employee("Nina", "Mayers", "111");
		Employee emp2 = new Employee("Tony", "Almeida", "222");

		emp1.setDepartment(department);
		emp2.setDepartment(department);

		session.save(emp1);
		session.save(emp2);

		session.getTransaction().commit();
		session.close();
	}

	@Test
	public void testWithConnection()
			throws ClassNotFoundException
	{
		// load the sqlite-JDBC driver using the current class loader
		Class.forName(TestSettings.SQLITE_JDBC_CLASS_NAME);

		Connection connection = null;
		try
		{
			// create a database connection
			connection = DriverManager.getConnection(TestSettings.getConnectionString());

			Statement statement = connection.createStatement();
			statement.setQueryTimeout(30); // set timeout to 30 sec.

			statement.executeUpdate("drop table if exists person");
			statement.executeUpdate("create table person (id integer, name string)");
			statement.executeUpdate("insert into person values(1, 'leo')");
			statement.executeUpdate("insert into person values(2, 'yui')");

			ResultSet rs = statement.executeQuery("select * from person");

			while (rs.next())
			{
				// read the result set
				System.out.println("name = " + rs.getString("name"));
				System.out.println("id = " + rs.getInt("id"));
			}
		}
		catch (SQLException e)
		{
			// if the error message is "out of memory",
			// it probably means no database file is found
			System.err.println(e.getMessage());
		}
		finally
		{
			try
			{
				if (connection != null)
					connection.close();
			}
			catch (SQLException e)
			{
				// connection close failed.
				System.err.println(e);
			}
		}
	}

}
